﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Shop.Logic
{
    public class DAO
    {
        OracleConnectionStringBuilder ocsb = new OracleConnectionStringBuilder(); //создаем объект построителя строки соединения
        OracleConnection connection;
        OracleCommand cmd;
        public DAO()
        {
            ocsb.UserID = "user2"; //задаем имя пользователя на сервере БД
            ocsb.Password = "user2";
            ocsb.Unicode = true;
            ocsb.DataSource = string.Format("(DESCRIPTION = (ADDRESS = " +
                "(Protocol = tcp)(Host={0})" + "(PORT = 1521))(CONNECT_DATA = " + "(SERVICE_NAME={1})))", "127.0.0.1", "XE"); // строка, задающая параметры соединения
            connection = new OracleConnection(ocsb.ConnectionString); //создаем новое подключение к серверу
        }

        public bool isExists(String cmds)
        {
            List<String> temp = ExecuteParams(cmds);
            if (temp.Count > 0)
                return true;
            return false;
        }

        public bool ControlData(String command)
        {
            try
            {
                cmd = new OracleCommand(command, connection);
                connection.Open();
                cmd.Transaction = connection.BeginTransaction(System.Data.IsolationLevel.Serializable); 
                cmd.ExecuteNonQuery();
                cmd.Transaction.Commit();
                return true;
            }
            catch (Exception e)
            {
                List<Object> tempEx = new List<Object>();
                tempEx.Add("Исключение " + e.Message);
                Shop.Program.DataIO.ToView(tempEx);
                return false;
            }
            finally
            {
                connection.Close();
            }
        }

        public Object ObjectReturn(String command)
        {
            Object temp = ReadOne(command);
            if (temp != null)
                return temp;
            return null;
        }

        private DataTable ReadOne(String cmds)
        {
            try
            {

                OracleDataAdapter da = new OracleDataAdapter(cmds, connection);
                connection.Open();
                DataTable dt = new DataTable("Source");
                da.Fill(dt);
                connection.Close();
                return dt;
            }
            catch (Exception e)
            {
                List<Object> tempEx = new List<Object>();
                tempEx.Add("Исключение "+e.Message);
                Shop.Program.DataIO.ToView(tempEx);
            }
            finally
            {
                connection.Close();
            }
            return null;

        }

        public List<String> ExecuteParams(String command)
        {
            cmd = new OracleCommand(command, connection);
            List<String> temp = new List<string>();

            connection.Open();
            OracleDataReader reader = cmd.ExecuteReader();
            try
            {
                while (reader.Read())
                {
                    reader.GetSchemaTable();
                    switch (reader.GetDataTypeName(0))
                    {
                        case "VARCHAR2":
                            temp.Add(reader.GetString(0));
                            break;
                        case "NUMBER":
                            temp.Add(reader.GetInt32(0).ToString());
                            break;
                    }
                }
            }
            catch (Exception e)
            {
                List<Object> tempEx = new List<Object>();
                tempEx.Add("Исключение " + e.Message);
                Shop.Program.DataIO.ToView(tempEx);
            }
            finally
            {
                reader.Close();
                connection.Close();
            }
            return temp;
        }


        public int ExecuteScalar(String command)
        {
            cmd = new OracleCommand(command, connection);
            int temp=0;
            object obj;
            try
            {
                connection.Open();
                obj = cmd.ExecuteScalar();
                temp = int.Parse(obj.ToString());
                return temp;
                
            }
            catch (Exception e)
            {
                List<Object> tempEx = new List<Object>();
                tempEx.Add("Исключение " + e.Message);
                Shop.Program.DataIO.ToView(tempEx);
            }
            finally
            {
              
                connection.Close();
            }
            return temp;
        }

        public static bool isexists(string cmds)
        {
            DAO dao = new DAO();
            List<String> temp = dao.ExecuteParams(cmds);
            if (temp.Count > 0)
                return true;
            return false;
        }

        public static int Scalar(string cmds)
        {
            DAO dao = new DAO();
            int scalar = dao.ExecuteScalar(cmds);
            if (scalar > 0)
                return scalar;
            return 0;
        }


    }

    
}
